package com.elink.enginerring.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

public class OperateExcelUtil {

	/**
	 * 导入excel文件
	 * @param file
	 * @return
	 */
//	public List<Fortune> importExcel(MultipartFile file) {
//		List<Fortune> list = new ArrayList<Fortune>();
//		try {
//			Workbook wb = null;
//			try {
//				// 获得excel文档对象，访问所有格式下的excel文件
//				wb = WorkbookFactory.create(file.getInputStream());
//			} catch (InvalidFormatException e) {
//				e.printStackTrace();
//			}
//			
//			Sheet sheet = wb.getSheetAt(0);
//			String id= "";
//			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
//				// 获得一行的对象
//				Row row = sheet.getRow(i);
//				Fortune info = new Fortune();
//
//				row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
//				id = IDGenerator.gen32UUID();
//				info.setId(id);
//
//				info.setHoroscope(row.getCell(1).getStringCellValue());
//				info.setType(Integer.parseInt(row.getCell(2).getStringCellValue()));
//				// 设置单元格的类型
//				row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC);
//				row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC);
//				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//				info.setStartDate(row.getCell(3).getDateCellValue());
//				info.setEndDate(row.getCell(4).getDateCellValue());
//				// 为日期格式化
//				sdf.format(info.getStartDate());
//				sdf.format(info.getEndDate());
//
//				row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
//				row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
//				row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
//				// 设置各个单元格的值
//				info.setlucky(Integer.parseInt(row.getCell(5).getStringCellValue()));
//				info.setColor(row.getCell(6).getStringCellValue());
//				info.setNumber(row.getCell(7).getStringCellValue());
//				info.setMatchHoroscope(row.getCell(8).getStringCellValue());
//				info.setDescription(row.getCell(9).getStringCellValue());
//
//				list.add(info);
//				
//				//数据同步
//				Datasync datasync = new Datasync();
//				
//				datasync.setId(IDGenerator.gen32UUID());
//				datasync.setType(DatasyncOperate.fortune);
//				datasync.setRecordId(id);
//				datasync.setOperate(DatasyncOperate.add);
//				datasync.setTime(new Date().getTime());
//				datasyncMapper.insert(datasync);
//				
//			}
//		} catch (IOException e) {
//			e.printStackTrace();
//		}
//		return list;
//	}

	/**
	 * 生成excel模板
	 * @throws IOException
	 */
	public void createExcelModel(OutputStream os) throws IOException {
		//创建excel文档对象
		Workbook wb = new HSSFWorkbook();
		HSSFSheet sheet = (HSSFSheet) wb.createSheet("sheet1");
		HSSFRow row = sheet.createRow(0);

		HSSFCell cell = row.createCell(0);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue("序号");

		HSSFCell cell1 = row.createCell(1);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell1.setCellValue("星座");

		HSSFCell cell2 = row.createCell(2);
		cell2.setCellValue("类型");

		HSSFCell cell3 = row.createCell(3);
		HSSFCellStyle cellStyle = (HSSFCellStyle) wb.createCellStyle();
		HSSFDataFormat format = (HSSFDataFormat) wb.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
		cell.setCellStyle(cellStyle);
		cell3.setCellValue("开始时间");

		HSSFCell cell4 = row.createCell(4);
		cell4.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell4.setCellValue("结束时间");

		HSSFCell cell5 = row.createCell(5);
		cell5.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell5.setCellValue("运势");

		HSSFCell cell6 = row.createCell(6);
		cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell6.setCellValue("幸运颜色");

		HSSFCell cell7 = row.createCell(7);
		cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell7.setCellValue("幸运数字");

		HSSFCell cell8 = row.createCell(8);
		cell8.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell8.setCellValue("适配星座");

		HSSFCell cell9 = row.createCell(9);
		cell9.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell9.setCellValue("综合描述");

		try {
			wb.write(os);
			os.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}
}
